ALTER TABLE `analys_translation` CHANGE COLUMN `id` `parameter_id` int(11) NOT NULL
GO

-- test version

CREATE TABLE dic_version (
    version_id     	varchar(25) NOT NULL,
    name_ru	varchar(25) NULL,
    name_en	varchar(25) NULL,
    name_kk	varchar(25) NULL,
    PRIMARY KEY(version_id)
)
ENGINE = InnoDB
AUTO_INCREMENT = 0
GO

INSERT INTO `fs1`.`dic_version`(`version_id`, `name_ru`)
VALUES('VERSION_1', 'версия 1')
GO

INSERT INTO `fs1`.`dic_version`(`version_id`, `name_ru`)
VALUES('VERSION_2', 'версия 2')
GO
INSERT INTO `fs1`.`dic_version`(`version_id`, `name_ru`)
VALUES('VERSION_3', 'версия 3')
GO

CREATE TABLE test_parametrs_units (
    parameter_id	int(11) NULL,
    unit	varchar(25) NULL,
    version      	varchar(25) NULL,
    analysis_instr_invest_list_id      	int(11) NOT NULL
    )
ENGINE = InnoDB
AUTO_INCREMENT = 0
GO
ALTER TABLE test_parametrs_units
    ADD CONSTRAINT unq_ptv
	UNIQUE (parameter_id, unit, version)
GO
ALTER TABLE test_parametrs_units
    ADD CONSTRAINT vp
	FOREIGN KEY(version)
	REFERENCES dic_version(version_id)
	ON DELETE RESTRICT
	ON UPDATE RESTRICT
GO
ALTER TABLE test_parametrs_units
    ADD CONSTRAINT up
	FOREIGN KEY(unit)
	REFERENCES units(combination_key)
	ON DELETE RESTRICT
	ON UPDATE RESTRICT
GO
ALTER TABLE test_parametrs_units
    ADD CONSTRAINT tstFK
	FOREIGN KEY(analysis_instr_invest_list_id)
	REFERENCES dic_analysis_instr_invest_list(analysis_instr_invest_list_id)
	ON DELETE RESTRICT
	ON UPDATE RESTRICT
GO
ALTER TABLE test_parametrs_units
    ADD CONSTRAINT tp
	FOREIGN KEY(parameter_id)
	REFERENCES analys_translation(parameter_id)
	ON DELETE RESTRICT
	ON UPDATE RESTRICT
GO
CREATE INDEX tstFK
    ON test_parametrs_units(analysis_instr_invest_list_id)
GO
CREATE UNIQUE INDEX unq_ptv
    ON test_parametrs_units(parameter_id, unit, version)
GO
CREATE INDEX vp
    ON test_parametrs_units(version)
GO
CREATE INDEX up
    ON test_parametrs_units(unit)
GO


CREATE TABLE test_versions (
    analysis_instr_invest_list_id	int(11) NOT NULL,
    version_id	varchar(25) NOT NULL,
    phase_id  	varchar(25) NOT NULL
    )
ENGINE = InnoDB
AUTO_INCREMENT = 0
GO
ALTER TABLE test_versions
    ADD CONSTRAINT uniqVTP
	UNIQUE (analysis_instr_invest_list_id, version_id, phase_id)
GO
ALTER TABLE test_versions
    ADD CONSTRAINT versionFK
	FOREIGN KEY(version_id)
	REFERENCES dic_version(version_id)
	ON DELETE RESTRICT
	ON UPDATE RESTRICT
GO
ALTER TABLE test_versions
    ADD CONSTRAINT testFK
	FOREIGN KEY(analysis_instr_invest_list_id)
	REFERENCES test_parametrs_units(analysis_instr_invest_list_id)
	ON DELETE RESTRICT
	ON UPDATE RESTRICT
GO
ALTER TABLE test_versions
    ADD CONSTRAINT phaseFK
	FOREIGN KEY(phase_id)
	REFERENCES dic_phase(phase_id)
	ON DELETE RESTRICT
	ON UPDATE RESTRICT
GO
CREATE INDEX versionFK
    ON test_versions(version_id)
GO
CREATE INDEX phaseFK
    ON test_versions(phase_id)
GO
CREATE UNIQUE INDEX uniqVTP
    ON test_versions(analysis_instr_invest_list_id, version_id, phase_id)
GO


INSERT INTO dic_units(unit, name_ru, name_en, name_kk)
  VALUES('EMPTY', 'EMPTY', 'EMPTY', 'EMPTY')
GO
INSERT INTO dic_units(unit, name_ru, name_en, name_kk)
  VALUES('G', 'г', 'g', 'г')
GO
INSERT INTO dic_units(unit, name_ru, name_en, name_kk)
  VALUES('KG', 'кг', 'kg', 'кг')
GO
INSERT INTO dic_units(unit, name_ru, name_en, name_kk)
  VALUES('LITRE', 'л', 'l', 'l')
GO
INSERT INTO dic_units(unit, name_ru, name_en, name_kk)
  VALUES('MG', 'мг', 'mg', 'мг')
GO
INSERT INTO dic_units(unit, name_ru, name_en, name_kk)
  VALUES('MKMOLE', 'мкмоль', 'mkmole', 'мкмоль')
GO
INSERT INTO dic_units(unit, name_ru, name_en, name_kk)
  VALUES('ML', 'мл', 'ml', 'мл')
GO
INSERT INTO dic_units(unit, name_ru, name_en, name_kk)
  VALUES('MMOLE', 'ммоль', 'mmole', 'ммоль')
GO
INSERT INTO dic_units(unit, name_ru, name_en, name_kk)
  VALUES('UNIT', 'ед', 'unit', 'ед')
GO


INSERT INTO units(combination_key, numerator, denominator)
  VALUES('G_LITRE', 'G', 'LITRE')
GO
INSERT INTO units(combination_key, numerator, denominator)
  VALUES('MG_KG', 'MG', 'KG')
GO
INSERT INTO units(combination_key, numerator, denominator)
  VALUES('MKMOLE_LITRE', 'MKMOLE', 'LITRE')
GO
INSERT INTO units(combination_key, numerator, denominator)
  VALUES('ML_KG', 'ML', 'KG')
GO
INSERT INTO units(combination_key, numerator, denominator)
  VALUES('MMOLE_LITRE', 'MMOLE', 'LITRE')
GO
INSERT INTO units(combination_key, numerator, denominator)
  VALUES('UNIT', 'UNIT', 'EMPTY')
GO
INSERT INTO units(combination_key, numerator, denominator)
  VALUES('UNIT_LITRE', 'UNIT', 'LITRE')
GO


ALTER TABLE test_parametrs_units
	ADD COLUMN min_value double NOT NULL,
	ADD COLUMN max_value double NOT NULL
GO

ALTER TABLE test_parametrs_units MODIFY COLUMN parameter_id int(11) NOT NULL
GO
ALTER TABLE test_parametrs_units MODIFY COLUMN unit varchar(25) NOT NULL
GO
ALTER TABLE test_parametrs_units MODIFY COLUMN version varchar(25) NOT NULL
GO
ALTER TABLE test_parametrs_units MODIFY COLUMN min_value double NULL
GO
ALTER TABLE test_parametrs_units MODIFY COLUMN max_value double NULL
GO